/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package All.Mapper;

import All.DTO.AccountDTO;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author NGUYEN DANG
 */
public class AccountMapper extends MapperDB 
{

    public AccountMapper() throws Exception 
        {
		super();
	}
        public boolean login(String username, String password) throws Exception {
		boolean found = false;
		try {
                        StringBuffer sql = new StringBuffer();
			sql.append("SELECT * FROM User WHERE ");
			sql.append(" Username = '" + username + "'");
			sql.append(" AND Password = '" + password + "'");
			
			PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
			ResultSet rs = stmt.executeQuery();
			if ((rs != null) && rs.next())
				found = true;
			stmt.close();
		}
		catch (Exception e) {
			throw e;
		}
		
		return found;
	}

    public AccountDTO getUserInformation(String username) throws Exception {
		AccountDTO accountDTO = new AccountDTO();
		
		try {
			StringBuilder sql = new StringBuilder();
			sql.append("SELECT * FROM User WHERE ");
			sql.append(" Username = '" + username + "'");
		
			PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
			ResultSet rs = stmt.executeQuery();
			if ((rs != null) && rs.next()) {
				initAccountDTO(accountDTO, rs);
			}
			
			stmt.close();
		}
		catch (Exception e) {
			throw e;
		}
		
		return accountDTO;
	}

    public void initAccountDTO(AccountDTO accountDTO, ResultSet rs) throws Exception {
		if ((rs != null) && (accountDTO != null)) {
			accountDTO.setUsername(rs.getString("Username"));
                        accountDTO.setPassword(rs.getString("Password"));
                        accountDTO.setHoten(rs.getString("Hoten"));
                        accountDTO.setNgaysinh(rs.getString("Ngaysinh"));
                        accountDTO.setGioitinh(rs.getString("Gioitinh"));                       
                        accountDTO.setEmail(rs.getString("Email"));
                        accountDTO.setDienthoai(rs.getString("Dienthoai"));
                        //accountDTO.setDiachi(rs.getString("Diachi"));
                        accountDTO.setNgaythamgia(rs.getString("Ngaythamgia"));
                        accountDTO.setRole(rs.getString("Role"));
		}
	}
    public ArrayList<AccountDTO> listUser() throws Exception {
		ArrayList<AccountDTO> listOfUsers = new ArrayList<AccountDTO>();
		AccountDTO userDTO = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT * FROM User");
			PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
			ResultSet rs = stmt.executeQuery();
			while ((rs != null) && rs.next()) {
				userDTO = new AccountDTO();
				initAccountDTO(userDTO, rs);
				listOfUsers.add(userDTO);
			}
			
			stmt.close();
		}
		catch (Exception e) {
			throw e;
		}
		
		return listOfUsers;
    }

    public boolean ADupdate(AccountDTO accountDTO) throws Exception {
        boolean found = false;
        try{
            StringBuilder sql = new StringBuilder();
            sql.append("UPDATE User set Password='" + accountDTO.getPassword() + "',");
            sql.append(" Hoten='" + accountDTO.getHoten() + "',");
            sql.append(" Ngaysinh='" + accountDTO.getNgaysinh() + "',");
            sql.append(" Gioitinh='" + accountDTO.getGioitinh() + "',");
            sql.append(" Email='" + accountDTO.getEmail() + "',");            
            sql.append(" Dienthoai='" + accountDTO.getDienthoai() + "',");
            sql.append(" Ngaythamgia='" + accountDTO.getNgaythamgia() + "',");
            sql.append(" Role='" + accountDTO.getRole() + "'");
            sql.append(" where Username='"+ accountDTO.getUsername() + "'");
            
            
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            int flag = stmt.executeUpdate();
            if(flag == 1)
                    found = true;
            stmt.close();
        }
        catch(Exception e)
        {
            throw e;
        }
        return found;
    }

    public boolean insert(AccountDTO accountDTO) throws Exception {
        boolean found = false;
        try{
            StringBuilder sql = new StringBuilder();
            sql.append("insert into User(Username,Password,Hoten,Ngaysinh,Gioitinh,Email,Dienthoai,Ngaythamgia,Role) values(");
            sql.append("'" + accountDTO.getUsername() +"',");
            sql.append("'" + accountDTO.getPassword() +"',");
            sql.append("'" + accountDTO.getHoten() +"',");
            sql.append("'" + accountDTO.getNgaysinh() +"',");
            sql.append("'" + accountDTO.getGioitinh() +"',");           
            sql.append("'" + accountDTO.getEmail() +"',");
            sql.append("'" + accountDTO.getDienthoai() +"',");
            sql.append("'" + accountDTO.getNgaythamgia() +"',");
            sql.append("'" + accountDTO.getRole() +"')");
            
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            int flag = stmt.executeUpdate();
            if(flag == 1)
                    found = true;
            stmt.close();
        }
        catch(Exception e)
        {
            throw e;
        }
        return found ;
    }

    public boolean ADDeleteUser(String username) throws Exception {
        boolean found = false;
        try{
            StringBuilder sql = new StringBuilder();
            sql.append("Delete from User where Username=");
            sql.append("'"+username+"'");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            int flag = stmt.executeUpdate();
            if(flag == 1)
                    found = true;
            stmt.close();
        }
        catch(Exception e)
        {
            throw e;
        }
        return found;
    }
    
}
